/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       PostgreSqlExecutor.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/12/29      feiwe                         add
 */


package com.fengwei.executor;

import cn.hutool.core.util.CharsetUtil;
import cn.hutool.crypto.symmetric.SymmetricAlgorithm;
import cn.hutool.crypto.symmetric.SymmetricCrypto;
import com.alibaba.druid.pool.DruidDataSource;
import com.fengwei.bean.DataSourcePoolManager;
import com.fengwei.bean.Page;
import com.fengwei.config.CommonProperty;
import com.fengwei.util.GsonUtil;
import com.fengwei.util.HexStringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service("postgresqlExecutor")
public class PostgreSqlExecutor extends AbstractDbExecutor {
    private final static Logger logger = LoggerFactory.getLogger(MysqlExecutor.class);

    @Autowired
    private DataSourcePoolManager dataSourcePoolManager;

    @Autowired
    private CommonProperty commonProperty;

    @Override
    public String genUrl(String databaseType, String databaseHost, Integer databasePort, String databaseDbname) {
        String url = "jdbc:" + databaseType + "://" + databaseHost + ":" + databasePort + "/" + databaseDbname;
        return url;
    }

    @Override
    public Connection getConnection(String dbId, String url, String userName, String passWord) throws ClassNotFoundException, SQLException {
        DruidDataSource druidDataSource = dataSourcePoolManager.getDataSourcePoolManagerMap().get(dbId);
        if (null == druidDataSource) {
            druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(url);
            druidDataSource.setDriverClassName(DataSourcePoolManager.POSTGRESQL_DRIVER);
            druidDataSource.setUsername(userName);
            SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.AES, HexStringUtil.hexStringToBytes(commonProperty.getDatabaseAesKey()));
            String passWordDec = aes.decryptStr(passWord, CharsetUtil.CHARSET_UTF_8);
            druidDataSource.setPassword(passWordDec);
            druidDataSource.setInitialSize(commonProperty.getDatabaseInitialSize());
            druidDataSource.setMaxActive(commonProperty.getDatabaseMaxActive());
            druidDataSource.setMinIdle(commonProperty.getDatabaseMinIdle());
            druidDataSource.setMaxWait(2000);
            druidDataSource.setPoolPreparedStatements(true);
            druidDataSource.setMaxOpenPreparedStatements(20);
            druidDataSource.setValidationQuery("SELECT 1");
            druidDataSource.setTestWhileIdle(false);
            druidDataSource.setTestOnBorrow(false);
            druidDataSource.setTestOnReturn(true);
            druidDataSource.init();
            dataSourcePoolManager.getDataSourcePoolManagerMap().put(dbId, druidDataSource);
        }
        Connection connection = druidDataSource.getConnection();
        return connection;
    }

    @Override
    public List getAllTables(Connection connection, String name) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String finalSql = " select tablename from pg_tables where schemaname = 'public' order by tablename";
        List tableList = new ArrayList<>();
        try {
            pstmt = connection.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tableList.add(rs.getString(1));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            rs.close();
            pstmt.close();
        }
        return tableList;
    }

    @Override
    public Page<Map<String, Object>> executeSelectSql(Page<Map<String, Object>> page, Connection connect, String sql) throws Exception {
        int pageNo = page.getPageNo();
        int pageSize = page.getPageSize();
        int limitFrom = (pageNo - 1) * pageSize;
        sql = sql.trim();
        String finalSql = " select * from ( " + sql + " ) tab  limit " + pageSize + " offset " + limitFrom;
//        if ((sql.indexOf("show") == 0) || (sql.indexOf("SHOW") == 0)) {
//            finalSql = sql;
//        }

        List<Map<String, Object>> list = super.queryForList(connect, finalSql);
        logger.debug("GsonUtil.toJson(list,false):" + GsonUtil.toJson(list, false));
        int rowCount = super.executeQueryForCount2(connect, sql);
        logger.debug("rowCount:" + rowCount);
        List<Map<String, Object>> columnTypeList = super.executeSqlForColumns(connect, sql);
        Map columnTypeMap = new HashMap();
        List<Map<String, Object>> tempList = new ArrayList<>();
        for (Map<String, Object> map : columnTypeList) {
//            logger.debug(map.get("column_name") + ":" + map.get("data_type"));
            columnTypeMap.put(map.get("column_name"), ((String) map.get("data_type")).toLowerCase());

            Map<String, Object> map2 = new HashMap<>();
            map2.put("field", map.get("column_name"));
            map2.put("title", map.get("column_name"));
            map2.put("sortable", Boolean.valueOf(true));
//            if (map.get("data_type").equals("DATETIME")) {
//                map2.put("editor", "datetimebox");
//            } else if ((map.get("data_type").equals("INT")) || (map.get("data_type").equals("SMALLINT")) || (map.get("data_type").equals("TINYINT"))) {
//                map2.put("editor", "numberbox");
//            } else if (map.get("data_type").equals("DOUBLE")) {
//                map2.put("editor", "numberbox");
//            } else if ((!map.get("data_type").equals("BLOB")) && (!map.get("data_type").equals("CLOB"))) {
//                if ((!map.get("data_type").equals("VARBINARY")) && (!map.get("data_type").equals("BINARY"))) {
//                    map2.put("editor", "text");
//                }
//            }
            tempList.add(map2);
        }

        String primaryKey = "";
        String tableName = "";
        String temp = "";

        page.setTotalCount(rowCount);
        page.setResult(list);
        page.setColumns(tempList);
        page.setColumnType(columnTypeMap);
        //page.setPrimaryKey(primaryKey);
        page.setTableName(tableName);

        return page;
    }


    @Override
    public String ddl(Connection connection, String tableName, String userName) throws Exception {
        return "暂不支持";
    }

    @Override
    public Map getPrimaryKey(String tableName, Connection connection) throws Exception {
        String finalSql = "SELECT\n" +
                "   A.ordinal_position,\n" +
                "   A.column_name,\n" +
                "   CASE A.is_nullable WHEN 'NO' THEN 0 ELSE 1 END AS is_nullable,\n" +
                "   A.data_type,\n" +
                "   coalesce(A.character_maximum_length, A.numeric_precision, -1) as length,\n" +
                "   A.numeric_scale,\n" +
                "   CASE WHEN length(B.attname) > 0 THEN 1 ELSE 0 END AS is_pk\n" +
                "FROM\n" +
                "   information_schema.columns A\n" +
                "LEFT JOIN (\n" +
                "    SELECT\n" +
                "        pg_attribute.attname\n" +
                "    FROM\n" +
                "        pg_index,\n" +
                "        pg_class,\n" +
                "        pg_attribute\n" +
                "    WHERE\n" +
                "        pg_class.oid = '" + tableName + "' :: regclass\n" +
                "    AND pg_index.indrelid = pg_class.oid\n" +
                "    AND pg_attribute.attrelid = pg_class.oid\n" +
                "    AND pg_attribute.attnum = ANY (pg_index.indkey)\n" +
                ") B ON A.column_name = b.attname\n" +
                "WHERE\n" +
                "   A.table_schema = 'public'\n" +
                "AND A.table_name = '" + tableName + "'\n" +
                "ORDER BY\n" +
                "   ordinal_position ASC";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map mapKey = new HashMap();
        try {
            pstmt = connection.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String columnName = rs.getString("column_name");
                int isPk = rs.getInt("is_pk");
                if (isPk == 1) {
                    mapKey.put(columnName, "1");
                }
            }
            return mapKey;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            rs.close();
            pstmt.close();
        }
    }
}
